package com.css.fxfzypg.modules.SettingSeismic.repository.impl;

import com.css.fxfzypg.base.dict.repository.entity.DictEntity;
import com.css.fxfzypg.base.response.RestResponse;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.SettingSeismic.entity.*;
import com.css.fxfzypg.modules.SettingSeismic.repository.SettingSeismicRepository;
import com.css.fxfzypg.modules.SettingSeismic.vo.SetSeiPointVo;
import com.css.fxfzypg.modules.SettingSeismic.vo.SettingSeismicDetailsVO;
import com.css.fxfzypg.modules.resultupload.repository.entity.ResultMainEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.google.common.primitives.Ints;
import com.google.gson.JsonObject;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: fxfzypg
 * @Author: lhl
 * @CreateDate: 2022/10/13 17:11
 */
@Repository
public class SettingSeismicRepositoryImpl implements SettingSeismicRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public int saveSettingSeismicFault(SettingSeismicFault entity) {
        String sql = "insert into setting_seismic_fault(sf_id,coordinates,benchmark_magnitude,angle,step_size," +
                "operation_magnitude,setting_seismic_number,setting_seismic,task_id,create_time,create_user_id," +
                "create_user,is_delete,is_angle,space_range,distance_fault,interval_fault,is_magnitude,earthquake_type,grids_size)" +
                "values (:sfId,:coordinates,:benchmarkMagnitude,:angle,:stepSize,:operationMagnitude," +
                ":settingSeismicNumber,:settingSeismic,:taskId,:createTime,:createUserId,:createUser,:isDelete," +
                ":isAngle,:spaceRange,:distanceFault,:intervalFault,:isMagnitude,:earthquakeType,:gridsSize)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int saveSettingSeismicPoint(SettingSeismicPoint entity) {
        String sql = "insert into setting_seismic_point(ss_id,coordinates,benchmark_magnitude,angle,step_size,sf_id," +
                "operation_magnitude,point_seismic_number,determine_magnitude,task_id,create_time,create_user_id," +
                "create_user,is_delete,is_angle,space_range,earthquake_type,distance_fault,interval_fault,is_magnitude,geom)" +
                "values (:ssId,:coordinates,:benchmarkMagnitude,:angle,:stepSize,:sfId,:operationMagnitude," +
                ":pointSeismicNumber,:determineMagnitude,:taskId,:createTime,:createUserId,:createUser,:isDelete," +
                ":isAngle,:spaceRange,:earthquakeType,:distanceFault,:intervalFault,:isMagnitude,ST_GeometryFromText(:geom,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int saveSettingSeismicDetails(SettingSeismicDetails entity) {
        String sql = "insert into setting_seismic_details(id,longitude,latitude,macro_position,earthquake_level," +
                "fault_tangent_angle,angle,ss_id,is_delete,fault_num)" +
                "values(:id,:longitude,:latitude,:macroPosition,:earthquakeLevel,:faultTangentAngle,:angle,:ssId," +
                ":isDelete,:faultNum)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public SettingSeismicFault getSettingSeismicFaultById(String id) {
        String sql = "select * from setting_seismic_fault where is_delete ='" + YNEnum.N.toString() + "' and sf_id='" + id + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SettingSeismicFault.class));
    }

    @Override
    public List<SettingSeismicFault> getSettingSeismicFaultByTaskId(String taskId) {
        String sql = "select * from setting_seismic_fault where is_delete ='" + YNEnum.N.toString() +
                "' and task_id='" + taskId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SettingSeismicFault.class));
    }

    @Override
    public SettingSeismicPoint getSettingSeismicPointById(String id) {
        String sql = "select * from setting_seismic_point where is_delete ='" + YNEnum.N.toString() + "' and ss_id='" + id + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SettingSeismicPoint.class));
    }

    @Override
    public List<SettingSeismicPoint> getPointsByTaskId(String taskId) {
        String sql = "select * from setting_seismic_point " +
                "where is_delete ='" + YNEnum.N.toString() + "' and task_id ='" + taskId
                + "' order by coordinates";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SettingSeismicPoint.class));
    }

    @Override
    public int updateFaultNum(SettingSeismicPoint settingSeismicPoint) {
        String pointSql = "update setting_seismic_point set fault_num= '" + settingSeismicPoint.getFaultNum() +
                "' where ss_id= '" + settingSeismicPoint.getSsId() + "'";
        int i = jdbcTemplate.update(pointSql);
        String detailsSql = "update setting_seismic_details set fault_num= '" + settingSeismicPoint.getFaultNum() +
                "' where ss_id= '" + settingSeismicPoint.getSsId() + "'";
        int l = jdbcTemplate.update(detailsSql);
        return i+l;
    }

    @Override
    public List<SettingSeismicPoint> getSettingSeismicPointBySfIdOrderByFaultNum(String sfId) {
        String sql = "select a.* from setting_seismic_point a left join setting_seismic_details b on a.ss_id=b.ss_id " +
                "where a.is_delete ='" + YNEnum.N.toString() + "' and b.is_delete ='" + YNEnum.N.toString()
                + "' and a.sf_id='" + sfId + "' order by b.fault_num desc";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SettingSeismicPoint.class));
    }

    @Override
    public List<SettingSeismicPoint> getSettingSeismicPointByTaskId(String taskId) {
        String sql = "select * from setting_seismic_point where is_delete ='" + YNEnum.N.toString() +
                "' and task_id='" + taskId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SettingSeismicPoint.class));
    }

    @Override
    public SettingSeismicDetails getSettingSeismicDetailsById(String id) {
        String sql = "select * from setting_seismic_details where is_delete ='" + YNEnum.N.toString() +
                "' and id='" + id + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
    }
    //zyt--根据id查询数据
    @Override
    public SettingSeismicDetails getSettingSeismicDetailsById2(String id) {
        String sql = "select * from setting_seismic_details where id='" + id + "'";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
    }

    @Override
    public List<SettingSeismicDetails> getSettingSeismicDetailsBySsId(String id) {
        String sql = "select * from setting_seismic_details where is_delete ='" + YNEnum.N.toString() +
                "' and ss_id='" + id + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
    }

    @Override
    public List<SettingSeismicDetails> getSettingSeismicDetailsByTaskId(String taskId) {
        String sql = "select a.* from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id " +
                "and b.is_delete='" + YNEnum.N.toString() + "'and a.is_delete='" + YNEnum.N.toString() + "' " +
                "where b.task_id='" + taskId + "'  order by a.fault_num,a.earthquake_level";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
    }

    @Override
    public SettingSeismicPoint getSettingSeismicPointBySdId(String id) {
        String sql = "select a.* from setting_seismic_point a left join setting_seismic_details b on a.ss_id=b.ss_id " +
                "where a.is_delete ='" + YNEnum.N.toString() + "' and b.is_delete ='" + YNEnum.N.toString()
                + "' and b.id='" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SettingSeismicPoint.class));
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public List<String> getPointDetailByTaskId(String taskId) {
        String sql = "select b.task_id|| '-' ||a.fault_num|| '-'||a.earthquake_level|| '-' ||a.angle" +
                " from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id " +
                "and b.is_delete='" + YNEnum.N.toString() + "'and a.is_delete='" + YNEnum.N.toString() + "' " +
                "where b.task_id='" + taskId + "'";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public SettingSeismicDetails getMaxNum(String taskId) {
        String sql = "select a.* from setting_seismic_details a" +
                " left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='" + YNEnum.N.toString() +
                "'and a.is_delete='" + YNEnum.N.toString() + "' where b.task_id='" + taskId + "' order by a.fault_num desc limit 1";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public int updateSettingSeismicPoint(SettingSeismicPoint entity) {
        String sql = "update setting_seismic_point set coordinates=:coordinates," +
                "benchmark_magnitude=:benchmarkMagnitude,angle=:angle,step_size=:stepSize," +
                "operation_magnitude=:operationMagnitude,point_seismic_number=:pointSeismicNumber," +
                "determine_magnitude=:determineMagnitude,ss_id=:ssId,update_time=:updateTime,update_user=:updateUser," +
                "is_delete =:isDelete,is_angle=:isAngle,is_magnitude=:isMagnitude," +
                "distance_fault=:distanceFault,interval_fault=:intervalFault,geom=ST_GeometryFromText(:geom,4490),fault_num=:faultNum " +
                " where ss_id=:ssId and is_delete='" + YNEnum.N.toString() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public int deleteSettingSeismicFault(String id, String userId, Timestamp updateTime) {
        String sql = "update setting_seismic_fault set is_delete=?,update_user=?,update_time=? where sf_id=?";
        return jdbcTemplate.update(sql, YNEnum.Y.toString(), userId, updateTime, id);
    }

    @Override
    public int deleteSettingSeismicPoint(String id, String userId, Timestamp updateTime) {
        String sql = "update setting_seismic_point set is_delete=?,update_user=?,update_time=? where ss_id=?";
        return jdbcTemplate.update(sql, YNEnum.Y.toString(), userId, updateTime, id);
    }

    //zyt--根据任务ID逻辑删除point
    @Override
    public int deleteSettingSeismicPoint2(String id, String userId, Timestamp updateTime,Integer faultNum) {
        String sql = "update setting_seismic_point set is_delete=?,update_user=?,update_time=? where task_id=? " +
                "and fault_num='" + faultNum + "'";
        return jdbcTemplate.update(sql, YNEnum.Y.toString(), userId, updateTime, id);
    }

    @Override
    public int deleteSettingSeismicPointBySfId(String sfId, String taskId) {
        String sql = "delete from setting_seismic_point where sf_id=? and task_id=?";
        return jdbcTemplate.update(sql, sfId, taskId);
    }

    @Override
    public int deleteSettingSeismicDetails(String ssId) {
        String sql = "update setting_seismic_details set is_delete ='" + YNEnum.Y.toString() + "'" +
                " where ss_id='" + ssId + "'  and is_delete='" + YNEnum.N.toString() + "'";
        int i = jdbcTemplate.update(sql);
        return i;
    }

    @Override
    public int deleteByFaultNum(String taskId, Integer faultNum) {
        String sql = "update setting_seismic_details as a set is_delete ='" + YNEnum.Y.toString() + "'" +
                " from setting_seismic_point as b  where a.ss_id=b.ss_id" +
                " and a.fault_num='" + faultNum + "'  and a.is_delete='" + YNEnum.N.toString() + "' and b.task_id= '" + taskId + "'";
        int i = jdbcTemplate.update(sql);
        return i;
    }

    @Override
    public int deleteById(String id) {
        String sql = "update setting_seismic_details set is_delete ='" + YNEnum.Y.toString() + "'" +
                " where id='" + id + "'  and is_delete='" + YNEnum.N.toString() + "'";
        int i = jdbcTemplate.update(sql);
        return i;
    }

    @Override
    public int updateFaultNum1(String ssId, Integer faultNum) {
        String sql = "update setting_seismic_point set fault_num=fault_num-1" +
                " where fault_num>'" + faultNum + "' and ss_id= '" + ssId + "'";
        int i = jdbcTemplate.update(sql);
        return i;
    }
    @Override
    public int updateFaultNum2(String taskId, Integer faultNum) {
        String sql = "update setting_seismic_details as a set fault_num=a.fault_num-1" +
                " from setting_seismic_point as b  where a.ss_id=b.ss_id" +
                " and a.fault_num>'" + faultNum + "' and b.task_id= '" + taskId + "'";
        int i = jdbcTemplate.update(sql);
        return i;
    }
    //更新point表序号
    @Override
    public int updateFaultNum3(String taskId) {
        String sql = "update setting_seismic_point as a set fault_num=b.fault_num" +
                " from setting_seismic_details as b  where a.ss_id=b.ss_id and a.task_id= '" + taskId + "'";
        int i = jdbcTemplate.update(sql);
        return i;
    }

    @Override
    public List<SettingSeismicDetailsVO> setEarthquakeCensus(List<String> taskIdList, List<String> placeProvince) {
        StringBuilder sql = new StringBuilder("select a.fault_num,a.ss_id,longitude,latitude,macro_position,count(longitude) " +
                "from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='0' " +
                "and a.is_delete= '" + YNEnum.N.toString() + "'");
        String sqlStr = "";
        StringBuilder whereSql = new StringBuilder(" where 1=1");
        if (!PlatformObjectUtils.isEmpty(taskIdList)) {
            whereSql.append(" and b.task_id in (:taskIdList)");
        }
        if (!PlatformObjectUtils.isEmpty(placeProvince)) {
            if(placeProvince.size()==1){
                whereSql.append(" and macro_position like '%").append(placeProvince.get(0)).append("%'");
                sqlStr = whereSql.toString();
            }else if(placeProvince.size()>1){
                whereSql.append(" and (");
                for (String province : placeProvince) {
                    whereSql.append(" macro_position like '%").append(province).append("%' or");
                }
                int index = whereSql.lastIndexOf("or");
                sqlStr = whereSql.substring(0, index);
                sqlStr+=" )";
            }

        }
        StringBuilder groupSql = new StringBuilder(" GROUP BY a.fault_num,a.ss_id,longitude,latitude,macro_position");
        StringBuilder orderSql = new StringBuilder(" ORDER BY a.fault_num");
        sql.append(sqlStr).append(groupSql).append(orderSql);
        Map idMap = new HashMap();
        idMap.put("taskIdList", taskIdList);
        return namedParameterJdbcTemplate.query(sql.toString(), idMap, new BeanPropertyRowMapper<>(SettingSeismicDetailsVO.class));
    }
    @Override
    public List<SettingSeismicDetailsVO> setEarthquakeCensus(String taskId, String placeProvince) {
        StringBuilder sql = new StringBuilder("select a.fault_num,a.ss_id,b.task_id,longitude,latitude,macro_position,count(longitude) " +
                "from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='0' " +
                "and a.is_delete= '" + YNEnum.N.toString() + "'");
        StringBuilder whereSql = new StringBuilder(" where 1=1");
        if (!PlatformObjectUtils.isEmpty(taskId)) {
            whereSql.append(" and b.task_id= '" + taskId + "'");
        }
        if (!PlatformObjectUtils.isEmpty(placeProvince)) {
            whereSql.append(" and macro_position like '%" + placeProvince + "%'");
        }
        StringBuilder groupSql = new StringBuilder(" GROUP BY a.fault_num,a.ss_id,b.task_id,longitude,latitude,macro_position");
        StringBuilder orderSql = new StringBuilder(" ORDER BY a.fault_num");
        sql.append(whereSql).append(groupSql).append(orderSql);

       /* String sql="select fault_num,a.ss_id,longitude,latitude,macro_position,count(longitude) from setting_seismic_details " +
                "a left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='0' and a.is_delete= '" + YNEnum.Y.toString() + "'" +
                "where b.task_id= ? GROUP BY fault_num,a.ss_id,longitude,latitude,macro_position";*/

        List<SettingSeismicDetailsVO> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SettingSeismicDetailsVO.class));
        return list;
    }

    @Override
    public List<SettingSeismicDetailsVO> setEarthquakeCensusNossid(String taskId, String placeProvince) {
        StringBuilder sql = new StringBuilder("select a.fault_num,longitude,latitude,macro_position,count(longitude) " +
                "from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='0' " +
                "and a.is_delete= '" + YNEnum.N.toString() + "'");
        StringBuilder whereSql = new StringBuilder(" where 1=1");
        if (!PlatformObjectUtils.isEmpty(taskId)) {
            whereSql.append(" and b.task_id= '" + taskId + "'");
        }
        if (!PlatformObjectUtils.isEmpty(placeProvince)) {
            whereSql.append(" and macro_position like '%" + placeProvince + "%'");
        }
        StringBuilder groupSql = new StringBuilder(" GROUP BY a.fault_num,a.ss_id,longitude,latitude,macro_position");
        StringBuilder orderSql = new StringBuilder(" ORDER BY a.fault_num");
        sql.append(whereSql).append(groupSql).append(orderSql);

       /* String sql="select fault_num,a.ss_id,longitude,latitude,macro_position,count(longitude) from setting_seismic_details " +
                "a left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='0' and a.is_delete= '" + YNEnum.Y.toString() + "'" +
                "where b.task_id= ? GROUP BY fault_num,a.ss_id,longitude,latitude,macro_position";*/

        List<SettingSeismicDetailsVO> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SettingSeismicDetailsVO.class));
        return list;
    }

    @Override
    public List<SettingSeismicDetailsVO> setEarthquakeCensus(String taskId) {
        String sql = "select a.* " +
                " from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='0' " +
                " and a.is_delete= '" + YNEnum.N.toString() + "'" + " where  b.task_id= '" + taskId + "'";
        return  jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SettingSeismicDetailsVO.class));
    }

    @Override
    public List<SettingSeismicDetailsVO> setEarthquakeCensus(List<String> taskIdList) {
        String sql = "select a.* " +
                " from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id and b.is_delete='0' " +
                " and a.is_delete= '" + YNEnum.N.toString() + "'" + " where  b.task_id in (:taskIdList)";
        Map idMap = new HashMap();
        idMap.put("taskIdList", taskIdList);
        return namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(SettingSeismicDetailsVO.class));
    }

    @Override
    public Map<String, Object> getDetailPage(SettingSeismicDetailsVO settingSeismicDetailsVO, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select a.* from setting_seismic_details a left join setting_seismic_point b on a.ss_id=b.ss_id ");
        /*StringBuilder orderSql = new StringBuilder(" order by dkp_create_time desc");*/
        StringBuilder whereSql = new StringBuilder(" where a.is_delete = '" + YNEnum.N.toString() + "' and b.is_delete = '" + YNEnum.N.toString() + "' ");
        StringBuilder orderBy=new StringBuilder(" order by earthquake_level");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;


        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(settingSeismicDetailsVO.getSsId())) {
            whereSql.append(" and b.ss_id = '" + settingSeismicDetailsVO.getSsId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(settingSeismicDetailsVO.getTaskId())) {
            whereSql.append(" and b.task_id = '" + settingSeismicDetailsVO.getTaskId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(settingSeismicDetailsVO.getLongitude())) {
            whereSql.append(" and longitude = '" + settingSeismicDetailsVO.getLongitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(settingSeismicDetailsVO.getLatitude())) {
            whereSql.append(" and latitude = '" + settingSeismicDetailsVO.getLatitude() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(settingSeismicDetailsVO.getEarthquakeLevel())) {
            whereSql.append(" and earthquake_level = '" + settingSeismicDetailsVO.getEarthquakeLevel() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(settingSeismicDetailsVO.getAngleStr())) {
            whereSql.append(" and a.angle = '" + Integer.valueOf(settingSeismicDetailsVO.getAngleStr()) + "'");
        }

        sql.append(whereSql).append(orderBy);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SettingSeismicDetails.class)).size();
        List<SettingSeismicDetails> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public Map<String, Object> getDetailsPage(SettingSeismicDetailsVO settingSeismicDetailsVO, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select a.* from setting_seismic_details a ");
        StringBuilder whereSql = new StringBuilder(" where a.is_delete = '" + YNEnum.N.toString() + "' and a.ss_id = '" +settingSeismicDetailsVO.getSsId() + "' ");
        StringBuilder orderBy=new StringBuilder(" order by a.earthquake_level");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderBy);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SettingSeismicDetails.class)).size();
        List<SettingSeismicDetails> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }


    @Override
    public SettingSeismicDetails getSettingSeismicDetailsByInfluenceflieId(String influenceflieId) {
        String sql = "select * from setting_seismic_details where id = (select source_earthquake_id from setting_seismic_influenceflied where uuid='" + influenceflieId + "')";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
    }

    @Override
    public List<SettingSeismicDetails> getByTaskNumAndLonAndLatAndEqLevel(String taskNum, BigDecimal lon, BigDecimal lat, BigDecimal eqLevel) {
        StringBuilder sql=new StringBuilder("SELECT t1.* FROM setting_seismic_details t1 left join setting_seismic_point t2 on t1.ss_id=t2.ss_id left join ypg_preassess_task t3 on t2.task_id=t3.id where 1=1 ");
        if (PlatformObjectUtils.isNotEmpty(taskNum)){
            sql.append("and t3.task_num='"+taskNum+"' and t1.longitude='"+lon+"' and t1.latitude='"+lat+"' and t1.earthquake_level='"+eqLevel+"'");
        }else {
            sql.append("and t1.longitude='"+lon+"' and t1.latitude='"+lat+"' and t1.earthquake_level='"+eqLevel+"'");
        }
       /* String sql = "SELECT t1.* FROM setting_seismic_details t1 left join setting_seismic_point t2 on t1.ss_id=t2.ss_id" +
                " left join ypg_preassess_task t3 on t2.task_id=t3.id" +
                " where t3.task_num='"+taskNum+"' and t1.longitude='"+lon+"' and t1.latitude='"+lat+"' and t1" +
                ".earthquake_level='"+eqLevel+"'";*/
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SettingSeismicDetails.class));
    }

    //根据影响场和任务空间数据求相交面积
    @Override
    public Integer getArea(String influencefliedId, String taskId) {
        try{
            StringBuilder sql  = new StringBuilder("select CEILING(st_area(ST_Transform(st_intersection(t.geom, st_geomfromtext((select st_astext(geom) geomText from ypg_preassess_task where id='"+taskId+"'),4490)),4527))/1000000) as disasterArea from seismic_influencefly_pbe t where t.influencefly_id='"+influencefliedId+"' and st_intersects(t.geom, st_geomfromtext((select st_astext(geom) geomText from ypg_preassess_task where id='"+taskId+"'),4490)) ");
            Integer area = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
            if(ObjectUtils.isNotEmpty(area)){
                return area;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    /* @Override
    public List<SetSeiPointVo> getDataByLongitudeAndLatitude(String epi_lon, String epi_lat, String i, String m) {
        String sql="select b.ss_id,b.earthquake_level,b.longitude,b.latitude,b.macro_position,c.id taskId from setting_seismic_point d " +
                "left join setting_seismic_details b on d.ss_id = b.ss_id left join ypg_preassess_task c on d.task_id=c.id " +
                "where st_intersects((select a.geom from district_boundary_county a " +
                "where st_intersects(st_geomfromtext('POINT("+epi_lon+" "+epi_lat+")',4490),a.geom)), d.geom) " +
                "or st_intersects((select st_transform(st_buffer(st_transform(st_geomfromtext('POINT("+epi_lon+" "+epi_lat+")',4490), 4527),"+i+"*1000),4490)), d.geom) " +
                "and d.is_delete='0' and b.is_delete='0' and c.del_flag='0' GROUP BY b.ss_id,b.earthquake_level ,b.longitude,b.latitude ,b.macro_position,c.id";

        List<SetSeiPointVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SetSeiPointVo.class));
        return list;
    }*/
}
